﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BiffenService.Model;
using System.Data.SqlClient;
using System.Data;

namespace BiffenService.Db
{
    class ShowDb: IShowDb
    {
        private static SqlCommand dbCmd = null;

        public List<Show> GetCurrentShows()
        {
            return GetCurrentShows(DateTime.Now);
        }

        public List<Show> GetCurrentShows(DateTime date)
        {
            List<Show> shows = new List<Show>();

            DateTime endDate = date.AddDays(14);


            string sql = @"SELECT * FROM bif_show WHERE StartTime BETWEEN @start AND @end";
            
            dbCmd = DbClient.GetDbCommand(sql);
            dbCmd.Parameters.AddWithValue("@start", date);
            dbCmd.Parameters.AddWithValue("@end", endDate);

            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Show show;
            IMovieDb movieDb = new MovieDb();
            List<Movie> movies;

            while (dbReader.Read())
            {
                show = new Show();
                show.StartTime = (DateTime)dbReader["StartTime"];
                show.Duration = (int)dbReader["Duration"];

                show.Id = (int)dbReader["Id"];

                shows.Add(show);
            }
            
            dbReader.Close();
            DbClient.Close();

            foreach (Show s in shows)
            {
                movies = movieDb.GetMoviesForShow(s.Id);
                s.setMovies(movies);
            }

            return shows;
        }


        public bool AddShow(Show show)
        {
            
            string sql = "INSERT INTO bif_show(StartTime, Duration, Description, Theater) VALUES (@start, @duration, @description, @theater) SET @Id = SCOPE_IDENTITY()";

            SqlCommand command = DbClient.GetDbCommand(sql);

            command.Parameters.AddWithValue("@start", show.StartTime);
            command.Parameters.AddWithValue("@duration", show.Duration);
            command.Parameters.AddWithValue("@description", show.Description);
            command.Parameters.AddWithValue("@theater", show.Theater.Id);

            command.Parameters.Add("@Id", SqlDbType.Int).Direction = ParameterDirection.Output;


            bool returnVal = command.ExecuteNonQuery() > 0;

            int id = (int) command.Parameters["@Id"].Value;

            show.Id = id;

            string sql2 = "INSERT INTO bif_show_has_movie(Show, Movie) VALUES (@showId, @movieId)";
            command = DbClient.GetDbCommand(sql2);

            command.Parameters.AddWithValue("@showId", show.Id);
            command.Parameters.AddWithValue("@movieId", show.Movies[0].Id);

            returnVal = command.ExecuteNonQuery() > 0; ;

            return returnVal;

        }

        public bool DeleteShow(Show show)
        {
            string sql = "DELETE FROM bif_show WHERE Id = @id";

            SqlCommand command = DbClient.GetDbCommand(sql);

            command.Parameters.AddWithValue("@id", show.Id);

            return command.ExecuteNonQuery() == 1;

        }



        public List<Show> GetShowsForMovie(int id)
        {
            string sql = "SELECT * FROM bif_show_has_movie AS H, bif_show AS S WHERE H.Show=S.Id AND H.Movie=@Id";
            SqlCommand cmd = DbClient.GetDbCommand(sql);
            cmd.Parameters.AddWithValue("@Id", id);

            List<Show> shows = new List<Show>();
            SqlDataReader r = cmd.ExecuteReader();

            while (r.Read())
            {
                Show s = new Show();

                s.Id = int.Parse(r["Id"].ToString());
                s.StartTime = DateTime.Parse(r["StartTime"].ToString());
                s.Theater = new Theater(int.Parse(r["Theater"].ToString()));

                shows.Add(s);
            }

            r.Close();
            DbClient.Close();

            TheaterDb theaterDb = new TheaterDb();

            foreach (var show in shows)
            {
                show.Theater = theaterDb.FindById(show.Theater.Id);
            }

            if (shows.Count > 0)
            {
                return shows;
            }
            return null;
        }

        public Show GetShowsById(int showId)
        {
            string sql = "SELECT * FROM bif_show WHERE Id = @showId";

            SqlCommand command = DbClient.GetDbCommand(sql);
            command.Parameters.AddWithValue("@showId", showId);

            IDataReader reader = command.ExecuteReader();
            Show show = new Show();

            while (reader.Read())
            {
         
                show.Id = (int)reader["Id"];
                show.StartTime = (DateTime)reader["StartTime"];
                show.Description = reader["Description"].ToString();
                Theater theater = new Theater((int)(reader["Theater"]));
                show.Theater = theater;
                show.Duration = (int)reader["Duration"];
            }
            reader.Close();
            DbClient.Close();

            return show;
        }

        public List<Show> GetCurrentShowsSeven(DateTime date, int theaterId)
        {
            List<Show> shows = new List<Show>();

            DateTime endDate = date.AddDays(7);


            string sql = @"SELECT * FROM bif_show WHERE StartTime BETWEEN @start AND @end AND Theater = @theaterId";

            dbCmd = DbClient.GetDbCommand(sql);
            dbCmd.Parameters.AddWithValue("@start", date);
            dbCmd.Parameters.AddWithValue("@end", endDate);
            dbCmd.Parameters.AddWithValue("@theaterId", theaterId);

            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Show show;
            IMovieDb movieDb = new MovieDb();
            List<Movie> movies;

            while (dbReader.Read())
            {
                show = new Show();
                show.StartTime = (DateTime)dbReader["StartTime"];
                show.Duration = (int)dbReader["Duration"];

                show.Id = (int)dbReader["Id"];

                shows.Add(show);
            }

            dbReader.Close();
            DbClient.Close();

            foreach (Show s in shows)
            {
                movies =  movieDb.GetMoviesForShow(s.Id);
                s.setMovies(movies);
            }

            return shows;
        }


    }
}
